package com.weifly.jettycn.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.weifly.jettycn.TableConst;
import com.weifly.jettycn.domain.Catalog;

public class CatalogDao extends JdbcDaoSupport {

	/**
	 * 获得catalog列表
	 */
	public List<Catalog> getCatalogList(){
		final List<Catalog> list = new ArrayList<Catalog>();
		String sql = "select * from " + TableConst.TABLE_CATALOG;
		this.getJdbcTemplate().query(sql, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				while(rs.next()){
					Catalog c = populateCatalog(rs);
					list.add(c);
				}
				return null;
			}
			
		});
		return list;
	}
	
	/**
	 * 根据名称查找catalog
	 */
	public List<Catalog> findCatalogByName(String name){
		final List<Catalog> list = new ArrayList<Catalog>();
		String sql = "select * from " + TableConst.TABLE_CATALOG + " where c_name=?";
		this.getJdbcTemplate().query(sql, new Object[]{name}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				while(rs.next()){
					Catalog c = populateCatalog(rs);
					list.add(c);
				}
				return null;
			}
			
		});
		return list;
	}
	
	/**
	 * 插入catalog
	 */
	public void insertCatalog(final Catalog catalog){
		String sql = "insert into " + TableConst.TABLE_CATALOG + "(c_catalogid, c_parentid, c_name, b_leaf, n_order) values(?,?,?,?,?)";
		this.getJdbcTemplate().update(sql, new PreparedStatementSetter(){

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, catalog.getCatalogid());
				ps.setString(2, catalog.getParentid());
				ps.setString(3, catalog.getName());
				ps.setInt(4, catalog.isLeaf() ? 1 : 2);
				ps.setInt(5, catalog.getOrder());
			}
			
		});
	}
	
	private Catalog populateCatalog(ResultSet rs) throws SQLException {
		Catalog c = new Catalog();
		c.setCatalogid(rs.getString("c_catalogid"));
		c.setParentid(rs.getString("c_parentid"));
		c.setName(rs.getString("c_name"));
		c.setLeaf(rs.getInt("b_leaf")==1);
		c.setOrder(rs.getInt("n_order"));
		return c;
	}
	
	/**
	 * 更新catalog
	 */
	public void updateCatalog(final Catalog catalog){
		String sql = "update " + TableConst.TABLE_CATALOG + " set c_parentid=?, c_name=?, b_leaf=?, n_order=? where c_catalogid=?";
		this.getJdbcTemplate().update(sql, new PreparedStatementSetter(){

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, catalog.getParentid());
				ps.setString(2, catalog.getName());
				ps.setInt(3, catalog.isLeaf() ? 1 : 2);
				ps.setInt(4, catalog.getOrder());
				ps.setString(5, catalog.getCatalogid());
			}
			
		});
	}
	
	/**
	 * 删除catalog
	 */
	public void deleteCatalog(final String catalogid){
		String sql = "delete from " + TableConst.TABLE_CATALOG + " where c_catalogid=?";
		this.getJdbcTemplate().update(sql, new PreparedStatementSetter(){

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, catalogid);
			}
			
		});
	}
}
